package com.gopay.common.util.cbrmb.poi;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.io.Serializable;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.UUID;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.gopay.common.util.DateUtils;
import com.gopay.common.util.cbrmb.exporttools.ReflectUtils;

/**
 * @author Andronicus
 * @since 2015年11月30日
 */
public class PoiUtils {
	private final static Logger logger = LoggerFactory.getLogger(PoiUtils.class);
	private final static String SERVER_FILE_PATH = System.getProperty("user.dir");
	private final static String SERVER_FILE_SEPARATOR = System.getProperty("file.separator");
	private final static String DEFAULT_DIR = SERVER_FILE_PATH + SERVER_FILE_SEPARATOR + "tmp_excel";
	
	private String dir = DEFAULT_DIR;
	private IExcelFirstRowHandler firstRowHandler;
	private IExcelDataRowsHandler dataRowsHandler;
	
	private PoiUtils(){
		firstRowHandler = new DefaultFirstRowHandlerImpl();
	}
	
	public static PoiUtils getInstance(){
		return PoiUtilsInstanceFactory.newInstance;
	}
	
	private static class PoiUtilsInstanceFactory {
		public static PoiUtils newInstance = new PoiUtils();
	}
	
	public <T extends Serializable> String buildExcelFromTemplate(List<T> data, String templateFilePath) throws Exception {
		Workbook wb = WorkbookFactory.create(new File(templateFilePath));
		Sheet sheet = wb.getSheetAt(0);
		List<String> columnNames = firstRowHandler.firstRowProcess(sheet);
		dataRowsHandler.dataRowsProcess(sheet, columnNames, data);
		String fileName = UUID.randomUUID().toString().substring(0, 32);
		File dirFile = new File(dir);
		if (!dirFile.isDirectory() && !dirFile.exists()) {
			dirFile.mkdirs();
		}
		String target = dir + SERVER_FILE_SEPARATOR + fileName + ".xlsx";
		logger.debug("生成待下载的Excel文件:{}", target);
		OutputStream os = new FileOutputStream(target);
		wb.write(os);
		os.close();
		return target;
	}
	
	public <T extends Serializable> void responseExcelFile(HttpServletResponse response, List<T> data, String templateFilePath) throws Exception {
		Workbook wb = WorkbookFactory.create(new File(templateFilePath));
		Sheet sheet = wb.getSheetAt(0);
		List<String> columnNames = firstRowHandler.firstRowProcess(sheet);
		dataRowsHandler.dataRowsProcess(sheet, columnNames, data);
		logger.debug("将文件直接以流的形式返回页面...");
		response.reset();
		ServletOutputStream os = response.getOutputStream();
		Calendar c = Calendar.getInstance();
		String fileName = DateUtils.formatDate(c.getTime(), "yyyyMMddHHmmss");
		response.setHeader("Content-disposition", "attachment; filename="+fileName+".xlsx");
		response.setContentType("application/x-excel");
		wb.write(os);
		os.flush();
		os.close();
	}
	
	public <T extends Serializable> void responseExcelFile2(HttpServletRequest request, HttpServletResponse response, List<T> data, String templateFilePath) throws Exception {
		Workbook wb = WorkbookFactory.create(new File(templateFilePath));
		Sheet sheet = wb.getSheetAt(0);
		List<String> columnNames = firstRowHandler.firstRowProcess(sheet);
		dataRowsHandler.dataRowsProcess(sheet, columnNames, data);
		logger.debug("将文件直接以流的形式返回页面...");
		response.reset();
		ServletOutputStream os = response.getOutputStream();
		Calendar c = Calendar.getInstance();
		String fileName = "跨境人民币运单管理导出文件"+DateUtils.formatDate(c.getTime(), "yyyyMMddHHmmss");
		
		String userAgent = request.getHeader("user-agent").toLowerCase();
		if (userAgent != null) {
            if (userAgent.contains("firefox")) {
                // firefox有默认的备用字符集是西欧字符集
                fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
            } else if (userAgent.contains("webkit") && (userAgent.contains("chrome") || userAgent.contains("safari"))) {
                // webkit核心的浏览器,主流的有chrome,safari,360
                fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
            } else {
                // 新老版本的IE都可直接用URL编码工具编码后输出正确的名称,无乱码
                fileName = URLEncoder.encode(fileName, "UTF-8");
            }
        }
		response.setHeader("Content-disposition", "attachment; filename="+fileName+".xlsx");
		response.setContentType("application/x-excel");
		wb.write(os);
		os.flush();
		os.close();
	}

	/**
	 * @Description: 指定文件名fileName，指定列表列名行数rownum
	 * @param request
	 * @param response
	 * @param data
	 * @param templateFilePath
	 * @param fileName
	 * @param rownum
	 * @throws Exception 
	 * @author huangcheng
	 */
    public <T extends Serializable> void responseExcelFileWithFileName(HttpServletRequest request,
            HttpServletResponse response, List<T> data, String templateFilePath, String fileName, int rownum)
                    throws Exception {
        Workbook wb = WorkbookFactory.create(new File(templateFilePath));
        Sheet sheet = wb.getSheetAt(0);
        Row reportHead = sheet.getRow(rownum);
        List<String> columnNames = new ArrayList<String>(0);
        for (Cell cell : reportHead) {
            Comment comment = cell.getCellComment();
            if (comment != null) {
                columnNames.add(comment.getString().getString());
            }
        }
        this.dataRowsProcess(sheet, columnNames, data, rownum + 1);
        logger.debug("将文件直接以流的形式返回页面...");
        response.reset();
        ServletOutputStream os = response.getOutputStream();

        String userAgent = request.getHeader("user-agent").toLowerCase();
        if (userAgent != null) {
            if (userAgent.contains("firefox")) {
                // firefox有默认的备用字符集是西欧字符集
                fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
            } else if (userAgent.contains("webkit") && (userAgent.contains("chrome") || userAgent.contains("safari"))) {
                // webkit核心的浏览器,主流的有chrome,safari,360
                fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
            } else {
                // 新老版本的IE都可直接用URL编码工具编码后输出正确的名称,无乱码
                fileName = URLEncoder.encode(fileName, "UTF-8");
            }
        }
        response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xlsx");
        response.setContentType("application/x-excel");
        wb.write(os);
        os.flush();
        os.close();
    }

	/**
	 * @return the firstRowHandler
	 */
	public IExcelFirstRowHandler getFirstRowHandler() {
		return firstRowHandler;
	}

	/**
	 * @param firstRowHandler the firstRowHandler to set
	 */
	public void setFirstRowHandler(IExcelFirstRowHandler firstRowHandler) {
		this.firstRowHandler = firstRowHandler;
	}

	/**
	 * @return the dataRowsHandler
	 */
	public IExcelDataRowsHandler getDataRowsHandler() {
		return dataRowsHandler;
	}

	/**
	 * @param dataRowsHandler the dataRowsHandler to set
	 */
	public void setDataRowsHandler(IExcelDataRowsHandler dataRowsHandler) {
		this.dataRowsHandler = dataRowsHandler;
	}

	/**
	 * @return the dir
	 */
	public String getDir() {
		return dir;
	}

	/**
	 * @param dir the dir to set
	 */
	public void setDir(String dir) {
		this.dir = dir;
	}

    private <T extends Serializable> void dataRowsProcess(Sheet sheet, List<String> columnNames, List<T> data,
            int dataRowNum) throws Exception {

        for (int i = 0; i < data.size(); i++) {
            Row row = sheet.getRow(dataRowNum) == null ? sheet.createRow(dataRowNum) : sheet.getRow(dataRowNum);
            dataRowNum++;
            T child = data.get(i);
            for (int k = 0; k < columnNames.size(); k++) {
                String fieldname = columnNames.get(k);
                // modified by huangcheng 2017年2月13日10:34:51 pci敏感信息日志处理
                // System.out.println("columnName: " + fieldname);
                Object childProperty = ReflectUtils.getter(child, fieldname,
                        ReflectUtils.searchField(child, fieldname).getType());
                // System.out.println("property-value: " + childProperty);
                Cell cell = row.getCell(k) == null ? row.createCell(k, Cell.CELL_TYPE_STRING) : row.getCell(k);
                String cellValueString = null;
                if (childProperty instanceof Date) {
                    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    cellValueString = format.format((Date) childProperty);
                } else {
                    cellValueString = String.valueOf(childProperty == null ? "" : childProperty);
                }
                cell.setCellValue(new XSSFRichTextString(cellValueString));
            }
        }

    }
}
